package org.PropertyManageMentSystem.Manager;

import org.PropertyManageMentSystem.Data.Complaint;
import org.PropertyManageMentSystem.Data.Director;
import org.PropertyManageMentSystem.Data.MysqlConfig;
import org.PropertyManageMentSystem.Data.Scheduler;

import java.sql.*;
import java.util.ArrayList;

public class LeaderManager {
    private Connection conn;

    public LeaderManager(MysqlConfig mysqlConfig) {
        String url = mysqlConfig.getUrl();
        String user = mysqlConfig.getUser();
        String password = mysqlConfig.getPassword();

        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            //创建连接
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void addDirector(String name, String tel) {
        try {
            String sql = "Insert into director (name, tel) values(?, ?)";
            PreparedStatement stat = conn.prepareStatement(sql);

            stat.setString(1, name); //即将p.getName()所获得值赋给第1个问号的地方
            stat.setString(2, tel);//同理将getPrice()所获得的值赋给第2个问号地方
            //执行sql语句
            stat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void addScheduler(String name, String tel) {
        try {
            String sql = "Insert into scheduler (name, tel) values(?, ?)";
            PreparedStatement stat = conn.prepareStatement(sql);

            stat.setString(1, name); //即将p.getName()所获得值赋给第1个问号的地方
            stat.setString(2, tel);//同理将getPrice()所获得的值赋给第2个问号地方
            //执行sql语句
            stat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Long getSchedulerByNameTel(String name,String tel) throws SQLException {
        String sql = "Select * from scheduler where name=? and tel=?";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setString(1, name);
        pst.setString(2, tel);
        ResultSet rs = pst.executeQuery();
        if (rs.next()) {
            return rs.getLong("schedulerId");
        }
        else {
            return null;
        }
    }

    public Long getDirectorByNameTel(String name,String tel) throws SQLException {
        String sql = "Select * from director where name=? and tel=?";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setString(1, name);
        pst.setString(2, tel);
        ResultSet rs = pst.executeQuery();
        if (rs.next()) {
            return rs.getLong("directorId");
        }
        else {
            return null;
        }
    }

    ArrayList<Director> getDirector() {
        ArrayList<Director> directors = new ArrayList<Director>();
        try {
            String sql = "select * from director";
            PreparedStatement stat = conn.prepareStatement(sql);

            //因为executeQuery会有ResultSet的返回值
            ResultSet res = stat.executeQuery();

            //将res中的值取出给到集合里面
            while (res.next()) {
                Long directorId = res.getLong("directorId");
                String name = res.getString("name");
                String tel = res.getString("tel");

                Director director = new Director(directorId, name, tel);
                //添加到容器
                directors.add(director);
            }
            return directors;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    ArrayList<Scheduler> getScheduler() {
        ArrayList<Scheduler> schedulers = new ArrayList<Scheduler>();
        try {
            String sql = "select * from scheduler";
            PreparedStatement stat = conn.prepareStatement(sql);

            //因为executeQuery会有ResultSet的返回值
            ResultSet res = stat.executeQuery();

            //将res中的值取出给到集合里面
            while (res.next()) {
                Long schedulerId = res.getLong("schedulerId");
                String name = res.getString("name");
                String tel = res.getString("tel");

                Scheduler scheduler = new Scheduler(schedulerId, name, tel);
                //添加到容器
                schedulers.add(scheduler);
            }
            return schedulers;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}
